package de.dhbwstuttgart.vincon.telefoncompany.utils;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.db4o.Db4oEmbedded;
import com.db4o.ObjectContainer;
import com.db4o.config.EmbeddedConfiguration;

import de.dhbwstuttgart.vincon.telefoncompany.model.Call;
import de.dhbwstuttgart.vincon.telefoncompany.model.Customer;
import de.dhbwstuttgart.vincon.telefoncompany.model.Phone;

public class DbHelper {
	public static final File db4oFile = new File(
			System.getProperty("user.home") + File.separator
					+ "TelefonCompany.db4o");
	public static final File db4oIndexesFile = new File(
			System.getProperty("user.home") + File.separator
					+ "TelefonCompanyIndexes.db4o");
	public static final File h2File = new File(System.getProperty("user.home")
			+ File.separator + "TelefonCompany");
	public static final File h2WithIndexFile = new File(
			System.getProperty("user.home") + File.separator
					+ "TelefonCompanyIndexes");

	public static final int DB4O = 0;
	public static final int DB4OWITHINDEX = 1;
	public static final int H2 = 2;
	public static final int H2WITHINDEX = 3;

	private static ObjectContainer db4o = null;
	private static ObjectContainer db4oWithIndexes = null;
	private static Connection h2;
	private static Connection h2WithIndexes;

	public static void openDb(int databaseId) {
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		if (getDb4o() != null)
			getDb4o().close();
		if (getDb4oWithIndexes() != null)
			getDb4oWithIndexes().close();
		if (getH2() != null)
			try {
				getH2().close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		if (getH2WithIndexes() != null)
			try {
				getH2WithIndexes().close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

		switch (databaseId) {
		case DB4O:
			db4o = Db4oEmbedded.openFile(DbHelper.getConf(),
					DbHelper.db4oFile.getAbsolutePath());
			break;
		case DB4OWITHINDEX:
			db4oWithIndexes = Db4oEmbedded.openFile(DbHelper.getConfIndexes(),
					DbHelper.db4oIndexesFile.getAbsolutePath());
			break;
		case H2:
			try {
				h2 = DriverManager.getConnection(
						"jdbc:h2:" + h2File.getAbsolutePath(), "", "");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			break;
		case H2WITHINDEX:
			try {
				h2WithIndexes = DriverManager.getConnection("jdbc:h2:"
						+ h2WithIndexFile.getAbsolutePath(), "", "");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			break;
		default:
			break;
		}

	}

	public static ObjectContainer getDb4o() {
		return db4o;
	}

	public static ObjectContainer getDb4oWithIndexes() {
		return db4oWithIndexes;
	}

	private static EmbeddedConfiguration getConf() {
		EmbeddedConfiguration conf = Db4oEmbedded.newConfiguration();
		conf.file().blockSize(8);
		conf.common().activationDepth(5);
		return conf;
	}

	private static EmbeddedConfiguration getConfIndexes() {
		EmbeddedConfiguration conf = Db4oEmbedded.newConfiguration();
		conf.file().blockSize(8);
		conf.common().activationDepth(5);
		conf.common().objectClass(Customer.class).objectField("forename")
				.indexed(true);
		conf.common().objectClass(Customer.class).objectField("surname")
				.indexed(true);
		conf.common().objectClass(Phone.class).objectField("numer")
				.indexed(true);
		conf.common().objectClass(Call.class).objectField("callee")
				.indexed(true);
		conf.common().objectClass(Call.class).objectField("caller")
				.indexed(true);
		return conf;
	}

	public static void createDb(Connection conn) {
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("CREATE TABLE customer (id INT AUTO_INCREMENT, forename VARCHAR(20) NULL, surname VARCHAR(20) NULL, PRIMARY KEY (id));");
			stmt.executeUpdate("CREATE TABLE phone (id INT AUTO_INCREMENT,  number VARCHAR(20) NULL, customerNr INT NOT NULL, PRIMARY KEY (id));");
			stmt.executeUpdate("CREATE TABLE call (id INT AUTO_INCREMENT , caller INT NOT NULL, callee INT NOT NULL, start DATETIME NULL, end DATETIME NULL,  PRIMARY KEY (id));");
			stmt.executeUpdate("ALTER TABLE phone ADD FOREIGN KEY (customerNr) REFERENCES customer (id);");
			stmt.executeUpdate("ALTER TABLE call ADD FOREIGN KEY (caller) REFERENCES phone (id);");
			stmt.executeUpdate("ALTER TABLE call ADD FOREIGN KEY (callee) REFERENCES phone (id);");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void createDbWithIndex(Connection conn) {
		createDb(conn);
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("CREATE INDEX IDX_CUSTOMER_FORENAME on CUSTOMER (forename);");
			stmt.executeUpdate("CREATE INDEX IDX_CUSTOMER_SURNAME on CUSTOMER (surname);");
			stmt.executeUpdate("CREATE INDEX IDX_PHONE_NUMBER on PHONE (number);");
			stmt.executeUpdate("CREATE INDEX IDX_CALL_CALLEE on CALL (callee);");
			stmt.executeUpdate("CREATE INDEX IDX_CALL_CALLER on CALL (caller);");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Connection getH2WithIndexes() {
		return h2WithIndexes;
	}

	public static Connection getH2() {
		return h2;
	}

}
